package com.rat.datadictionary.service;


import com.rat.datadictionary.entity.DataDictionary;
import com.rat.datadictionary.util.TemplateSetSheetStyle;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;

/**
 * @Description: 下载一个上传空间的模板
 * @Author: Howe
 * @Date: 2020-06-28 13:33
 */
@Service
public class CreateExcelTemplateService {

    private XSSFWorkbook workbook;// 工作薄


    //初始化模板
    public void init() {
        workbook = new XSSFWorkbook();
    }


    /**
     * 创建Excel模板文件
     */
    public void makeTemplate(HttpServletResponse response, String[] title, List<List<DataDictionary>> dataDictionaryListGroup) throws Exception {
        // 初始化数据
        init();
        OutputStream outputStream = null;
        try {
            makeExcelBody(title, dataDictionaryListGroup);//创建表体
            String fileName = dataDictionaryListGroup.get(0).get(0).getTableSchema() + "数据库的数据字典.xlsx";
            response.setHeader("Content-Disposition", "attachment; filename*=utf-8'zh_cn'" + URLEncoder.encode(fileName, "UTF-8"));
            response.setContentType("application/vnd.ms-excel");
            outputStream = response.getOutputStream();
            workbook.write(outputStream);
            outputStream.flush();
        } catch (Exception ex) {
            throw new Exception(ex);
        } finally {
            if (outputStream != null) {
                outputStream.close();
            }
        }
    }



    /**
    * @Description: 创建表体
    * @Param:
    * @Return:
    * @Author: Howe
    * @Date: 2021/1/12 9:16
    **/
    public void makeExcelBody(String[] title, List<List<DataDictionary>> dataDictionaryListGroup) {

        for (List<DataDictionary> dataDictionaryList : dataDictionaryListGroup) {
            //创建工作表
            XSSFSheet sheet = workbook.createSheet(dataDictionaryList.get(0).getTableName());
            sheet.setDefaultColumnWidth(20);
            TemplateSetSheetStyle.setSheetCenterFrameStyle(workbook, sheet, 0, 4,0, title.length);
            TemplateSetSheetStyle.setSheetLeftFrameStyle(workbook, sheet, 4,dataDictionaryList.size() + 4,0, title.length);



            Row row = sheet.getRow(0);
            //创建标题样式
            Font titleFont = TemplateSetSheetStyle.getTitleFont(workbook);
            CellStyle titleCellStyle = workbook.createCellStyle();
            titleCellStyle.cloneStyleFrom(row.getCell(0).getCellStyle());
            titleCellStyle.setFont(titleFont);
            titleCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
            titleCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            titleCellStyle.setLocked(true);

            //标题内容样式
            CellStyle titleContentCellStyle = workbook.createCellStyle();
            titleContentCellStyle.cloneStyleFrom(row.getCell(0).getCellStyle());
            titleContentCellStyle.setAlignment(HorizontalAlignment.LEFT);


            row.setHeightInPoints(25);
            Cell cell = row.getCell(0);
            cell.setCellValue("表名称");
            cell.setCellStyle(titleCellStyle);
            CellRangeAddress region = new CellRangeAddress(0, 0, 1, title.length-1);
            sheet.addMergedRegion(region);
            cell = row.getCell(1);
            cell.setCellValue(dataDictionaryList.get(0).getTableName());
            cell.setCellStyle(titleContentCellStyle);


            row = sheet.getRow(1);
            cell = row.getCell(0);
            cell.setCellValue("数据库名称");
            cell.setCellStyle(titleCellStyle);
            region = new CellRangeAddress(1, 1, 1, title.length-1);
            sheet.addMergedRegion(region);
            cell = row.getCell(1);
            cell.setCellValue(dataDictionaryList.get(0).getTableSchema());
            cell.setCellStyle(titleContentCellStyle);

            row = sheet.getRow(2);
            cell = row.getCell(0);
            cell.setCellValue("表描述");
            cell.setCellStyle(titleCellStyle);
            region = new CellRangeAddress(2, 2, 1, title.length-1);
            sheet.addMergedRegion(region);
            cell = row.getCell(1);
            cell.setCellValue(dataDictionaryList.get(0).getTableComment());
            cell.setCellStyle(titleContentCellStyle);




            row = sheet.getRow(3);
            for (int i = 0; i < title.length; i++) {
                cell = row.getCell(i);
                cell.setCellValue(title[i]);
                cell.setCellStyle(titleCellStyle);
            }
            traverseData(dataDictionaryList, sheet);
        }

    }


    /**
     * @Description: 遍历数据
     * @Param:
     * @Return:
     * @Author: Howe
     * @Date: 2021/1/7 18:12
     **/
    private void traverseData(List<DataDictionary> dataDictionaryList, XSSFSheet sheet) {
        for (int i = 0; i < dataDictionaryList.size(); i++) {
            Row row = sheet.getRow(i + 4);
            row.getCell(0).setCellValue(dataDictionaryList.get(i).getColumnName());
            row.getCell(1).setCellValue(dataDictionaryList.get(i).getColumnType());
            row.getCell(2).setCellValue(dataDictionaryList.get(i).getColumnKey());
            row.getCell(3).setCellValue(dataDictionaryList.get(i).getColumnComment());
        }


    }
}
